<?php
/*
ShopEx48版本数据转换框架，
采用工厂流水线模式：
	1. 先将大体的数据转过来，
	2. 根据ShopEx系统要求进行修正
第一步一般只需要在每一项的$aMap进行修改即可，第二步要根据实际情况修改

*/
set_time_limit(0);
error_reporting(E_ALL^E_NOTICE);
require_once("config.php");
$link=mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name);

mysql_query("set names utf8");

?>
<html>
<head>
<title>ShopEx数据转换程序</title>
<META HTTP-EQUIV="content-type" CONTENT="text/html; charset=UTF-8">
</head>
<body style="font-size:16px;">

<?php

$do = new transfer();

//$do->goodsGimages();			//商品图片gimages
//$do->goodsTag();				//商品标签
//$do->goodsCategory();			//商品分类
//$do->goodsBrand();			//商品品牌
//$do->goodsType();				//商品类型
//$do->goodsDetail();			//商品详细
//$do->goodsKeywords();			//商品关键词
//$do->relaGoods();				//相关商品
//$do->goodsImage();			//商品图片
//$do->goodsComment();			//商品评论
//$do->rebuilidCatPath();		//修正分类路径
//$do->memberLevel();			//会员等级
//$do->memberDetail();			//会员详细
//$do->memberCustomPro();			//自定义注册项
//$do->convertMessage();		//消息
//$do->convertArticles();		//文章
//$do->convertdelivery();		//配送方式
//$do->convertOrders();			//订单


class transfer{


		//转换商品图片函数 function goodsImages()
		function goodsGimages()
		{
			//清空sdb_gimages表
			$this->truncate('ecs_goods_gallery');

			////////////////////////////请修改这个区域///////////////////////////////
			$aMap = array(
				'img_id'				=>'',
				'goods_id'				=>'',		//商品ID
				'img_url'				=>'',		//缩略图-小图
				'img_desc'				=>'',		//默认为空
				'thumb_url'				=>'',		//跟img_url一样 缩略图-小图
				'img_original'			=>''		//原始图-大图
			);
			$to = 'ecs_goods_gallery';
			$from = 'sdb_goods';
			$where = '1';
			$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
			$rs = $this->dbQ($insertsql);
			echo "商品图片转换完毕!<br>";
			return true;
		}

		function goodsType(){
		//清空商品类型表
		$this->truncate('ecs_goods_type');

		////////////////////////////请修改这个区域///////////////////////////////
		$aMap = array(
			'cat_id'				=>	'',				//类型ID
			'cat_name'				=>	'',				//类型名称
			'enabled'				=>	'1',			//是否启用
			'attr_group'			=>	''				//默认为空
		);
		$to = 'ecs_goods_type';
		$from = 'sorts';
		$where = '1=1';
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "商品类型转换完毕!<br>";
		return true;
	}

	function goodsCategory(){
		//清空商品分类表
		$this->truncate('ecs_category');

		///////////////////////请修改这个区域///////////////////////////
		$aMap = array(
			'cat_id'			=>	'',			//分类ID
			'cat_name'			=>	'',			//分类名称
			'keywords'			=>	'',			//关键词
			'cat_desc'			=>	'',			//分类描述
			'parent_id'			=>	'',			//父分类ID
			'sort_order'		=>	'',			//排序
			'template_file'		=>	'',			//默认为空
			'measure_unit'		=>	'',			//数量单位,默认为空
			'show_in_nav'		=>	'0',		//是否显示在导航栏
			'style'				=>	'',			//默认为空
			'is_show'			=>	'1',		//是否启用
			'grade'				=>	'0',			//价格分级	默认为0 不分级
			'filter_attr'		=>	'0'
		);
		$to = 'ecs_category';
		$from = 'gi_productscatalog1';
		$where = 'iStatus!=9';

		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "商品父分类转换完毕!<br>";

		/////////////////////////请修改这个区域////////////////////////
		$aMap = array(
			'cat_id'			=>	'',
			'cat_name'			=>	'',
			'keywords'			=>	'',
			'cat_desc'			=>	'',
			'parent_id'			=>	'',
			'sort_order'		=>	'',
			'template_file'		=>	'',
			'measure_unit'		=>	'',
			'show_in_nav'		=>	'',
			'style'				=>	'',
			'is_show'			=>	'',
			'grade'				=>	'',
			'filter_attr'		=>	''
		);
		$to = 'ecs_category';
		$from = 'gi_productscatalog2';
		$where = 'iStatus!=9';
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "商品子分类转换完毕!<br>";

		//修正catpath
		//$this->rebuilidCatPath();
	}


	function goodsBrand(){
		//清空商品品牌表
		$this->truncate('ecs_brand');

		////////////////////////////请修改这个区域///////////////////////////////
		$aMap = array(
			'brand_id'				=>	'',			//品牌ID
			'brand_name'			=>	'',			//品牌名称
			'brand_logo'			=>	'',			//品牌logo
			'brand_desc'			=>	'',			//品牌描述
			'site_url'				=>	'',			//品牌网站地址
			'sort_order'			=>	'',			//排序
			'is_show'				=>	''			//是否显示
		);
		$to = 'ecs_brand';
		$from = 'timesmark';
		$where = '1=1';
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "商品品牌转换完毕!<br>";
	}


	function goodsDetail(){
		/* 清空商品、商品扩展分类、商品属性、关联商品、组合商品、赠品 */
		$aTable = array(
			'ecs_goods',
			'ecs_goods_cat',
			'ecs_goods_attr',
			'ecs_link_goods',
		);
		$this->truncate($aTable);
		/////////////////////////////////////////插入商品表数据/////////////////////////////////
		$aMap = array(
			'goods_id'				=>'',			//商品ID
			'cat_id'				=>'',			//分类ID
			'goods_type'			=>'',			//商品类型
			'goods_sn'				=>'',			//货号
			'goods_name'			=>'',			//商品名称
			'goods_name_style'		=>'+',
			'click_count'			=>'',			//商品访问次数
			'brand_id'				=>'',			//品牌ID
			'provider_name'			=>'',			//供应商名称
			'goods_number'			=>'',			//库存
			'goods_weight'			=>'',			//重量
			'market_price'			=>'',			//市场价
			'shop_price'			=>'',			//销售价
			'promote_price'			=>'0',			//促销价
			'promote_start_date'	=>'0',			//促销开始时间
			'promote_end_date'		=>'0',			//促销结束时间
			'warn_number'			=>'',			//商品报警数量
			'keywords'				=>'',			//关键词
			'goods_brief'			=>'',			//简介
			'goods_desc'			=>'',			//详细描述
			'goods_thumb'			=>'',			//缩略图
			'goods_img'				=>'',			//详细页图
			'original_img'			=>'',			//原图
			'is_real'				=>'1',			//是否是实体商品 默认为1
			'extension_code'		=>'',
			'is_on_sale'			=>'1',			//是否上架 默认为1
			'is_alone_sale'			=>'1',			//是否单独销售 默认为1
			'integral'				=>'',			//积分
			'add_time'				=>'unix_timestamp()',			//添加时间
			'sort_order'			=>'',			//排序
			'is_delete'				=>'0',			//是否回收 默认为0
			'is_best'				=>'',			//精品
			'is_new'				=>'',			//新品
			'is_hot'				=>'',			//热销
			'is_promote'			=>'0',			//是否是促销商品  默认为0
			'bonus_type_id'			=>'0',
			'last_update'			=>'unix_timestamp()',			//最后更新时间
			'seller_note'			=>'',
			'give_integral'			=>'-1',			//默认为-1
			'rank_integral'			=>'-1'			//默认为-1
		);
		$to = 'ecs_goods';
		$from = 'gi_products';
		$where = '1=1 group by lProductsID ';
		//////////////////////////////////////////////////////////////////////////////////////////
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "商品转换完毕!<br>";
	}


	/* 会员等级表 */
	function memberLevel(){
		$this->truncate('ecs_user_rank');

		////////////////////////////请修改这个区域///////////////////////////////
		$aMap = array(
			'rank_id'			=>'',			//会员等级ID
			'rank_name'			=>'',			//会员等级名称
			'min_points'		=>'',			//积分最小限额
			'max_points'		=>'',			//积分最大限额
			'discount'			=>'',			//会员等级折扣
			'show_price'		=>'1',			//是否显示价格 默认为1
			'special_rank'		=>'0'			//特殊会员 默认为0
		);
		$to = 'ecs_user_rank';
		$from = 'timesmark';
		$where = '1=1';
		//////////////////////////////////////////////////////////

		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "会员等级转换完毕!<br>";

	}


	//会员详细
	function memberDetail(){
		 /* 清空会员、会员地址、会员红包、会员价格、帐户明细 */
		$aTable = array(
			'ecs_users',
			'ecs_user_address',
			'ecs_user_bonus',
			'ecs_member_price',
			'ecs_user_account'
		);
		$this->truncate($aTable);

		/*************** ecs_users 会员表 *******************/
		$aMap = array(
			'user_id'				=>'',			//会员ID
			'email'					=>'',			//Email
			'user_name'				=>'',			//用户名
			'password'				=>'',			//密码
			'question'				=>'',			//安全密码提问
			'answer'				=>'',			//安全密码回答
			'sex'					=>'',			//性别
			'birthday'				=>'',			//生日
			'user_money'			=>'',			//帐户金额
			'frozen_money'			=>'',			//冻结帐户金额
			'pay_points'			=>'',			//支付积分
			'rank_points'			=>'',			//积分排名
			'address_id'			=>'',			//地址ID
			'reg_time'				=>'unix_timestamp()',			//注册时间
			'last_login'			=>'unix_timestamp()',			//最后上线时间(数字型)
			'last_time'				=>'',			//最后上线时间(日期型)
			'last_ip'				=>'',			//最后登录IP
			'visit_count'			=>'',			//访问次数
			'user_rank'				=>'',			//会员等级ID
			'is_special'			=>'0',			//是否特殊会员
			'salt'					=>'0',
			'parent_id'				=>'0',			//父类ID
			'flag'					=>'0',			//标识
			'alias'					=>'',			//别名		默认为空
			'msn'					=>'',			//MSN号
			'qq'					=>'',			//QQ号
			'office_phone'			=>'',			//办公电话
			'home_phone'			=>'',			//家庭电话
			'mobile_phone'			=>'',			//手机
			'is_validated'			=>'1',			//是否确认
			'credit_line'			=>''			//最大消费
		);
		$to = 'ecs_users';
		$from = 'userlist';
		$where = '1=1';
		///////////////////////////////////////////////////////
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "会员转换完毕!<br>";

		/*************** ecs_user_address 会员收货地址表 *******************/
		$aMap = array(
			'address_id'			=>'',			//地址ID
			'address_name'			=>'',			//收件人姓名
			'user_id'				=>'',			//会员ID
			'consignee'				=>'',			//收件人姓名
			'email'					=>'',			//Email
			'country'				=>'',			//国家
			'province'				=>'',			//省份
			'city'					=>'',			//城市
			'district'				=>'',			//地区
			'address'				=>'',			//收货人地址
			'zipcode'				=>'',			//邮编
			'tel'					=>'',			//电话
			'mobile'				=>'',			//手机
			'sign_building'			=>'',			//签名单位 默认为空
			'best_time'				=>''			//最佳发送时间 默认为空
		);
		$to = 'ecs_user_address';
		$from = 'userlist';
		$where = '1=1';
		///////////////////////////////////////////////////////
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "会员收货地址转换完毕!<br>";

		/*************** ecs_member_price 会员价格表 *******************/
		$aMap = array(
			//'price_id'			=>'',
			'goods_id'			=>'',		//商品ID
			'user_rank'			=>'',		//会员等级ID
			'user_price'		=>''		//会员价格
		);
		$to = 'ecs_member_price';
		$from = 'userlist';
		$where = '1=1';
		///////////////////////////////////////////////////////
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "会员价格转换完毕!<br>";

		/*************** ecs_user_account 会员帐户表 *******************/
		$aMap = array(
			//'id'				=>'',
			'user_id'			=>'',		//会员ID
			'admin_user'		=>'',		//管理员
			'amount'			=>'',		//总额
			'add_time'			=>'',		//添加时间
			'paid_time'			=>'',		//支付时间
			'admin_note'		=>'',		//管理员备注
			'user_note'			=>'',		//用户备注
			'process_type'		=>'',		//处理类型
			'payment'			=>'',		//支付方式
			'is_paid'			=>''		//是否已支付
		);
		$to = 'ecs_user_account';
		$from = 'userlist';
		$where = '1=1';
		///////////////////////////////////////////////////////
		$insertsql = $this->getInsertSQL($aMap,$to,$from,$where);
		$rs = $this->dbQ($insertsql);
		echo "会员帐户转换完毕!<br>";
	}


	/**************** 工具函数区 ***************/
	//查询数据库
	function dbQ($sql,$show=true){
		if(is_string($sql)){
			$rs = mysql_query($sql) or  die("<br><font color=red>".mysql_error()."</font><hr>".$sql);
			if($show){
				if(strtoupper(substr($sql,0,6)) != "SELECT"){
					echo " <font color=red>该查询影响 ".mysql_affected_rows()." 行</font><br>";
				}else{
					echo " <font color=red>该查询影响 ".mysql_num_rows($rs)." 行</font><br>";
				}
			}
			return $rs;
		}else{
			return false;
		}
	}


	//
	function getInsertSQL($aMap,$to,$from,$where){
		if(!is_array($aMap)){
			return false;
		}else{
			$colStr = '( ';
			$valStr = ' ';
			foreach($aMap as $col=>$val){
				if(empty($val)) continue;
				$colStr .= '`'.$col.'`,';
				if(is_numeric($val)){
					$valStr .= "'".$val."',";
				}else{
					$valStr .= $val.",";
				}
			}
			//
			$colStr = rtrim($colStr,",").")";
			$valStr = rtrim($valStr,",")." ";
		}

		$sql = "INSERT INTO `".$to."` ".$colStr." SELECT ".$valStr." FROM ".$from." WHERE ".$where;

		return $sql;
	}

	function ubbtohtml(&$string) {
		$string = preg_replace("/\[(\/?b)\]/i","<\\1>",$string);
		$string = preg_replace("/\[(\/?u)\]/i","<\\1>",$string);
		$string = preg_replace("/\[(\/?i)\]/i","<\\1>",$string);
		$string = preg_replace("/\[(\/?center)\]/i","<\\1>",$string);
		$string = preg_replace("/\[align=([a-zA-Z]+)\]/i","<p align=\\1>",$string);
		$string = preg_replace("/\[(\/align)\]/i","<\\1>",$string);
		//$string = preg_replace("/\[url\=(.+)\](.+)\[\/url\]/i","<a href=\\1 target=\"_blank\">\\2</a>",$string);
		//$string = preg_replace("/\[url\](.+)\[\/url\]/i","<a href=\\1 target=\"_blank\">\\1</a>",$string);
		//替换掉[url]标签
		$string = preg_replace("/\[url\=(.+)\](.+)\[\/url\]/i","\\2",$string);
		$string = preg_replace("/\[url\](.+)\[\/url\]/i","\\1",$string);
		//统一给url加上链接
		$string = preg_replace("/(.*)\s*(http:\/\/\S+)\s*(.*)/i","<a href=\\2 target=_blank>\\2</a>",$string);
		//
		$string = preg_replace("/\[img\](.+)\[\/img\]/i","<a href=\\1 target=\"_blank\"><img src=\\1 onload=\"javascript:if(this.width>500)this.width=500\"/></a>",$string);
		$string = preg_replace("/\[color=(.+)\](.+)\[\/color\]/i","<font color=\\1>\\2</font>",$string);
		$string = preg_replace("/\[size=([0-9]{1})\](.+)\[\/size\]/i","<font size=\\1>\\2</font>",$string);
		$string = nl2br($string);
	}

	//清除表数据，可以传入数组或者字符串
	function truncate($table){
		if(is_array($table)){
			foreach($table as $val){
				$sql = "TRUNCATE $val";
				$this->dbQ($sql,false);
			}
		}else{
			$sql = "TRUNCATE $table";
			$this->dbQ($sql,false);
		}

		return true;
	}


	function rebuilidCatPath(){
		//修正cat_path
		//要根据cat_id进行排序，保证子分类要出现在母分类之后
		$sql = "select cat_id,parent_id,cat_path from sdb_goods_cat order by cat_id ASC";
		$rs = $this->dbQ($sql);
		while($row = mysql_fetch_array($rs)){
			//母分类

			if($row['parent_id'] == 0){
				$data['cat_path'][$row['cat_id']] = ',';
			}else{
				$data['cat_path'][$row['cat_id']] = $row['parent_id'].',';
				if($data['cat_path'][$row['parent_id']] != ',')
				$data['cat_path'][$row['cat_id']] = $data['cat_path'][$row['parent_id']].$data['cat_path'][$row['cat_id']];
			}
			$sql = "update sdb_goods_cat set cat_path='{$data['cat_path'][$row['cat_id']]}' where cat_id='{$row['cat_id']}'";
			$this->dbQ($sql,false);
		}
	}


	//根据商品id获取哈希值
	function getHashDir($goods_id){
		$goods_id = intval($goods_id);
		$goods_id = $goods_id % 10000;
		$pref = intval($goods_id / 100);
		$post = $goods_id % 100;
		//位数不够补0
		if($pref < 10){
			$pref = "0".$pref;
		}
		//位数不够补0
		if($post < 10){
			$post = "0".$post;
		}

		return $pref."/".$post;
	}
	//实现mkdir -p，自动创建不存在的父目录
	function mkdirp($dir){
		$aDir = explode("/",$dir);
		if(is_array($aDir)){
			$nowpos = getcwd();
			foreach($aDir as $v){
				if(!file_exists($v)){
					@mkdir($v,0755);
				}
				@chdir($v);
			}
			@chdir($nowpos);
		}
	}


		//生成标准商品编号
    function genGoodsBn($goods_id){
			$str = $goods_id.time().rand(1,10000);
			return 'BN-'.strtoupper(substr(md5($str),0,8));
    }

}
?>

</body>
</html>